package Actions;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.upload.FormFile;

import forms.degreeform;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*; 
import support.*;


public class verificationAction extends Action {
	
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		
		forms.degreeform a = (forms.degreeform) form;
		
		String more = request.getParameter("final");

		if(more.equals("Submit")){
			
			
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Connection conn = null;
			Statement stmt = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String instruc = null;
			int addid=0, nameid=0, counid=0, citizid=0, resid=0, degid=0, schlid=0, mainid=0, specid=0, majid=0;


			try {
				// Registering Postgresql JDBC driver
				Class.forName("org.postgresql.Driver");
				// Open a connection to the database	
				conn = DriverManager.getConnection(url,"postgres","postgres");
				// Create the statement
		        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		                    				ResultSet.CONCUR_UPDATABLE);

				
		        //give a APP ID to application, Starting 1000.
		        int appid = 999;
		        rs = stmt.executeQuery("SELECT AppID FROM MainTable");
		        while(rs.next()){
		                appid = rs.getInt("AppID");
		        }
		        appid++;
		        
		        a.setAppID(appid);
		        
				// store Names info into database
				instruc = "INSERT INTO NAME(NameID, First, Middle, Last)" +
				          "VALUES(?,?,?,?)";

				//find and last id # and +1
				nameid = 1;
				rs = stmt.executeQuery("SELECT NameID FROM NAME");
				while(rs.next())
				{
			        nameid = rs.getInt("NameID");
				}
				nameid++;

				ps = conn.prepareStatement( instruc );
				ps.setInt(1, nameid);
				ps.setString( 2, (request.getSession().getAttribute("fName")).toString() );
				ps.setString( 3, (request.getSession().getAttribute("mi")).toString() );
				ps.setString( 4, (request.getSession().getAttribute("lName")).toString() );
				ps.executeUpdate();
				
				// store Address info into database
				// if applicant lives in the U.S.
				if(request.getSession().getAttribute("residence").equals("United States") ||
				request.getSession().getAttribute("residence").equals("United States Virgin Islands"))
				{
				
					instruc = "INSERT INTO ADDRESS(AddID, Street, City, State," +
				          "Zip, AreaCode, Phone) VALUES(?,?,?,?,?,?,?)";
					ps = conn.prepareStatement( instruc );
					
		            //find and last id # and +1
		            addid = 0;
		            rs = stmt.executeQuery("SELECT AddID FROM ADDRESS");
		            while(rs.next()){
		                    addid = rs.getInt("AddID");
		            }
		            addid++;
					
					ps.setInt(1, addid);
					ps.setString( 2, (request.getSession().getAttribute("street")).toString() );
					ps.setString( 3, (request.getSession().getAttribute("city")).toString() );
					ps.setString( 4, (request.getSession().getAttribute("lstate")).toString() );
					ps.setString( 5, (request.getSession().getAttribute("zip")).toString() );
					ps.setString( 6, (request.getSession().getAttribute("areaCode")).toString() );
					ps.setString( 7, (request.getSession().getAttribute("pnumber")).toString() );
					ps.executeUpdate();
				}
				// otherwise, if applicant does not live in the U.S.
				else
				{
					instruc = "INSERT INTO ADDRESS(AddID, Street, City, AreaCode," +
			          "Phone) VALUES(?,?,?,?,?)";
					ps = conn.prepareStatement( instruc );
		            
		            //find and last id # and +1
		            addid = 0;
		            rs = stmt.executeQuery("SELECT AddID FROM ADDRESS");
		            while(rs.next())
		            {
		            	addid = rs.getInt("AddID");
		            }
		            addid++;
		            
					ps.setInt(1, addid);
					ps.setString( 2, (request.getSession().getAttribute("street")).toString() );
					ps.setString( 3, (request.getSession().getAttribute("city")).toString() );
					ps.setString( 4, (request.getSession().getAttribute("areaCode")).toString() );
					ps.setString( 5, (request.getSession().getAttribute("pnumber")).toString() );
					ps.executeUpdate();
				}

				// store citizenship, residence, residency info into database
				instruc = "INSERT INTO Country(CounID, Citizenship, Residence," +
						  " Residency) VALUES(?,?,?,?)";
				ps = conn.prepareStatement( instruc );
				
		        //find and last id # and +1
		        counid = 0;
		        rs = stmt.executeQuery("SELECT CounID FROM Country");
		        while(rs.next())
		        {
		        	counid = rs.getInt("CounID");
		        }
		        counid++;
		        
		        String citizen = request.getSession().getAttribute("citizen").toString();
		        
				citizid = 0;
				rs = stmt.executeQuery("SELECT ID FROM Countries WHERE Country = '" + citizen + "'");
				while( rs.next() )
				{
					citizid = rs.getInt("ID");
				}
				
				String residence = request.getSession().getAttribute("residence").toString();
				resid = 0;
				rs = stmt.executeQuery("SELECT ID FROM Countries WHERE Country = '" + residence + "'");
				while( rs.next() )
				{
					resid = rs.getInt("ID");
				}
				
				String residency = request.getSession().getAttribute("residency").toString();
				
				ps.setInt(1, counid);
				ps.setInt( 2, citizid );
				ps.setInt( 3, resid );
				ps.setString( 4, residency );
				ps.executeUpdate();
				
                int apptableid = 0;
                rs = stmt.executeQuery("SELECT app_id FROM Application");
                while(rs.next())
                {
                	if( rs.getInt("app_id") > apptableid )
                	{
                		apptableid = rs.getInt("app_id");
                	}
                }
                apptableid++;
				// store application relate info into database
				instruc = "INSERT INTO Application(app_id,AppID," +
						  " numRev, status, Grade1,"+
						  " Grade2, Grade3, ready) VALUES(?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement( instruc );
				ps.setInt(1, apptableid);
				ps.setInt( 2, appid );
				ps.setInt( 3, 0 );
				ps.setString( 4, "Pending" );
				ps.setInt( 5, 0 );
				ps.setInt( 6, 0 );
				ps.setInt(7, 0);
				ps.setString( 8, "NO" );
				ps.executeUpdate();
				
				
				// store degree info into database
				List<forms.degreecollection> degreeinfos = (ArrayList)(request.getSession().getAttribute("degreeCollections"));
				List<String> Schoolinfo = new ArrayList<String>(); 	
				List<String> Monthinfo =new ArrayList<String>();  
				List<String> Yearinfo = new ArrayList<String>(); 
				List<String> Titleinfo = new ArrayList<String>(); 
				List<String> GPAinfo = new ArrayList<String>(); 
				List<String> Discpinfo = new ArrayList<String>(); 
				List<String> Stateinfo = new ArrayList<String>(); 
				List<String> FileNameinfo = new ArrayList<String>();
				List<InputStream> Fileinfo = new ArrayList<InputStream>();
				List<Integer> Sizeinfo = new ArrayList<Integer>();

				for(int i=0; i<degreeinfos.size();i++){
					Schoolinfo.add(degreeinfos.get(i).getSchool());
					Monthinfo.add(degreeinfos.get(i).getDmonth());
					Yearinfo.add(degreeinfos.get(i).getDyear());
					Titleinfo.add(degreeinfos.get(i).getTitle());
					GPAinfo.add(degreeinfos.get(i).getGpa());
					Discpinfo.add(degreeinfos.get(i).getDiscp());
					Stateinfo.add(degreeinfos.get(i).getStuState());
					FileNameinfo.add(degreeinfos.get(i).getFileName());
					Fileinfo.add(degreeinfos.get(i).getMyFile());
					Sizeinfo.add(degreeinfos.get(i).getFileSize());
				}

				request.getSession().setAttribute("Schoolinfo", Schoolinfo);

		  		int discpid = 0, univid = 0, addNew = 0, numDisc = 0, numSpec = 0;
		  		String disc = null, spec = null;
		  
		  		// to handle when student has more than one degrees
				for(int i =0; i<Schoolinfo.size(); i++)
				{
					rs= stmt.executeQuery("SELECT * FROM Disciplines");
					while(rs.next())
					{				
						if( rs.getInt("ID") > discpid )
						{
							discpid = rs.getInt("ID");
						}
						
						// add only new discipline to the list of disciplines,
						// no duplicates!
						if( Discpinfo.get(i).equals(rs.getString("Discipline")) )
						{
							addNew = 0;
							break;
						}
						else
						{
							addNew = 1;
						}
					}
					discpid++;
					
					if( addNew == 1 )
					{
						instruc = "INSERT INTO Disciplines(ID,Discipline) VALUES(?,?)";
						ps = conn.prepareStatement( instruc );
						ps.setInt(1, discpid);
						ps.setString(2, Discpinfo.get(i) );
						ps.executeUpdate();			
					}

		            String loc = Stateinfo.get(i);
		            String univ = Schoolinfo.get(i);
					rs = stmt.executeQuery("SELECT * FROM Universities");
					while( rs.next() )
					{
						if( rs.getInt("ID") > univid )
						{
							univid = rs.getInt("ID");
						}
						
						if( Schoolinfo.get(i).equals(rs.getString("University")) )
						{
							addNew = 0;
							break;
						}
						else
						{
							addNew = 1;
						}
					}
					univid++;
					if( addNew == 1 )
					{
						instruc = "INSERT INTO Universities(ID,Location,University) VALUES(?,?,?)";
						ps = conn.prepareStatement( instruc );
						ps.setInt(1, univid);
						ps.setString(2, loc);
						ps.setString(3, univ);
						ps.executeUpdate();
					}
						
						instruc = "INSERT INTO Degree(DegID,School,Major,Month,Year," +
				          "Title,GPA,Trans_name,Trans_file,Trans_size) VALUES(?,?,?,?,?,?,?,?,?,?)";
						ps = conn.prepareStatement( instruc );
						
						 //find the last id # and +1
		                degid = 0;
		                rs = stmt.executeQuery("SELECT DegID FROM Degree");
		                while(rs.next())
		                {
		                	if( rs.getInt("DegID") > degid )
		                	{
		                		degid = rs.getInt("DegID");
		                	}
		                }
		                degid++;

		                schlid = 0;
		                rs = stmt.executeQuery("SELECT ID FROM Universities WHERE Location = '" + loc + "' AND University = '" + univ + "'");
		                while( rs.next() )
		                {
		                	schlid = rs.getInt("ID");
		                }
		                

		                String speci = request.getSession().getAttribute("spec").toString();
		                String maj = Discpinfo.get(i);
		                
		                majid = 0;
						rs = stmt.executeQuery("SELECT ID FROM Disciplines WHERE Discipline =  '" + maj + "'");
						while( rs.next() )
						{
							majid = rs.getInt("ID");
						}
		                
						ps.setInt(1,degid);
						ps.setInt( 2, schlid );
						ps.setInt( 3, majid );
						ps.setString( 4, Monthinfo.get(i));
						ps.setString( 5, Yearinfo.get(i) );
						ps.setString( 6, Titleinfo.get(i) );
						ps.setString( 7, GPAinfo.get(i) );
						ps.setString(8, FileNameinfo.get(i));
					    ps.setBinaryStream(9, Fileinfo.get(i), Sizeinfo.get(i));
					    ps.setInt(10, Sizeinfo.get(i));
						ps.executeUpdate();	
						
						specid = 0;
						rs = stmt.executeQuery("SELECT ID FROM Specializations WHERE Specialization = '" + speci + "'");
						while( rs.next() )
						{
							specid = rs.getInt("ID");
						}
						
						
		                //find and last id # and +1 for main table
		                mainid = 0;
		                rs = stmt.executeQuery("SELECT TableID FROM MainTable");
		                while(rs.next() )
		                {
		                        mainid = rs.getInt("TableID");
		                }
		                mainid++;
		                instruc = "INSERT INTO MainTable(TableID, AppID, NAMEID, AddID," +
		                		  "DegID, MajID, SpecID, CounID) VALUES(?,?,?,?,?,?,?,?)";
		                ps = conn.prepareStatement( instruc );
		                ps.setInt(1,mainid);
		                ps.setInt(2,apptableid);
		                ps.setInt(3,nameid);
		                ps.setInt(4,addid);
		                ps.setInt(5,degid);
		                ps.setInt(6,majid);
		                ps.setInt(7,specid);
		                ps.setInt(8,counid);
		                ps.executeUpdate();
				}//forloop		
				
				instruc = "UPDATE users SET APP = '"+ nameid
						+"' WHERE user_name ='"+request.getSession().getAttribute("uname")+"'";
                ps = conn.prepareStatement( instruc );
                ps.executeUpdate();

			} catch (SQLException e) 
			  { 
				System.out.println(e);
			  } catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		   
			// close connection
		    try {
				rs.close();
			    stmt.close();
			    ps.close();
			    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			
	    	return mapping.findForward("submit");
		}
		else{
	    	return mapping.findForward("cancel");
		}
	}
}